package com.css.fxfzypg.manage.building.repository.impl;

import com.css.fxfzypg.manage.building.repository.BuildingReqository;
import com.css.fxfzypg.manage.building.repository.entity.BuildingEntity;
import com.css.fxfzypg.manage.building.vo.BuildingParams;
import com.css.fxfzypg.util.PlatformObjectUtils;
import com.css.fxfzypg.util.PlatformSessionUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import javax.servlet.http.HttpServletRequest;
import java.lang.reflect.Field;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository
public class BuildingReqositoryImpl implements BuildingReqository {

    @Autowired
    JdbcTemplate jdbcTemplate;
    @Autowired
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Override
    @Transactional
    public boolean saveBuilding(BuildingEntity buildingEntity) {
        String sql = "insert into ypg_building_single values(:id,:taskCode,:taskName,:buildCode,:province,:provinceId,:city,:cityId,:county,:countyId,:country,:countryId," +
                ":longitude,:latitude,:intensity,:buildYears,:purpose,:layerNum,:height,:terrainConditions,:foundationCondition,:planarRule,:verticalRule,:roofComponent," +
                ":housingCondition,:degreeDescription,:buildingStructure,:seismicCapacity,:remarks,:picture,:preparer,:fillingTime,:highStructureType,:steelStructureType," +
                ":hntConnectionType,:hntStairsPosition,:hntColumnSectionSize,:hntBeamSectionSize,:hntWeakLink,:hntMaxColumnCpacing,:hntMaximumFloorHeight,:hntMaxSpanSeam," +
                ":zhFloorType,:zhRoofType,:zhWallMaterials,:zhWallThicknessInterior,:zhWallThicknessExterior,:zhMortarType,:zhMortarStrength,:zhBearingSystem,:zhBearingMode,:zhStaircaseLocation,:zhHoleCrner,:zhStructuralColumn,:zhRingBeam," +
                ":zmRoofType,:zmWallMaterials,:zmMortar,:zmBearingSystem,:zmWallOpeningArea,:zmStructuralColumn,:zmRingBeam," +
                ":soilHardMountain,:soilWall,:soilModeType,:soilWallRingBeam,:soilWallDuctileMaterial,:soilDoorWindowWidth,:soilMaxSpacing,:soilRoofForm,:soilRoofingMaterials,:soilRoofTyingMeasures," +
                ":woodStructureType,:woodColumnsBracing,:woodColumnExtension,:woodNode,:woodTipDiameter,:woodFoundationAnchoring,:woodGableWall,:woodEnclosureWall,:woodWallConnection," +
                ":stoneWallMaterials,:stoneLoadMode,:stoneFloorStructure,:stoneRoofType,:stoneMaximumSpacing,:stoneWallOpeningArea,:stoneWallConnection,:stoneRingBeam,:stoneFourStructural," +
                ":isSubmit,:delFlag,:createName,:createId,:createTime,:submitter,:submitterId,:submitTime)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(buildingEntity);
        return namedParameterJdbcTemplate.update(sql, source) > 0;
    }

    @Override
    public Map<String,Object> querySubmitOrNot(String submitFlag, String taskCode, int curPage, int pageSize, String userId) {
        try {
            StringBuilder sql = new StringBuilder("select * from ypg_building_single where  del_flag = '0' and create_id = '"+userId+"' and task_code ='"+taskCode+"' and is_submit ='"+submitFlag+"\'");
            if(submitFlag.equals("0")){
                sql.append(" order by create_time desc");
            }else{
                sql.append(" order by submit_time desc");
            }
            int firstIndex=(curPage-1) * pageSize;
            int lastIndex=curPage * pageSize;
            StringBuilder limitSql = new StringBuilder(" limit "+lastIndex+" offset "+ firstIndex);
            Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(BuildingEntity.class)).size();
            List<BuildingEntity> buildingEntityList = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(BuildingEntity.class));
            Map<String,Object> map=new HashMap();
            map.put("total",total);
            map.put("rows",buildingEntityList);
            return map;
        }catch (EmptyResultDataAccessException e){
            return null;
        }
    }

    @Override
    public boolean updateIsSubmit(Map<String,String> map) {
        String sql = " update ypg_building_single set is_submit = 1, submitter = ?, submitter_id = ?, submit_time = ? where id = ?";
        return jdbcTemplate.update(sql, map.get("userName"), map.get("userId"), new Date(), map.get("id")) > 0 ;
    }

    @Override
    public BuildingEntity queryData(String id) {
        String sql = "select * from ypg_building_single where del_flag = '0' and id = ?";
        try {
            BuildingEntity buildingEntity = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(BuildingEntity.class),id);
            return buildingEntity;
        }catch (EmptyResultDataAccessException e){
            return null;
        }
    }

    @Override
    public boolean modifyData(BuildingEntity buildingEntity) {
        StringBuilder sql = new StringBuilder();
        sql.append("update ypg_building_single set intensity=:intensity,build_years=:buildYears,purpose=:purpose,layer_num=:layerNum,height=:height,terrain_conditions=:terrainConditions,foundation_condition=:foundationCondition," +
                "planar_rule=:planarRule,vertical_rule=:verticalRule,roof_component=:roofComponent,housing_condition=:housingCondition,degree_description=:degreeDescription," +
                "building_structure=:buildingStructure,seismic_capacity=:seismicCapacity,remarks=:remarks,picture=:picture,preparer=:preparer,filling_time=:fillingTime," +
                "high_structure_type=:highStructureType,steel_structure_type=:steelStructureType,hnt_connection_type=:hntConnectionType,hnt_stairs_position=:hntStairsPosition," +
                "hnt_column_section_size=:hntColumnSectionSize,hnt_beam_section_size=:hntBeamSectionSize,hnt_weak_link=:hntWeakLink,hnt_max_column_cpacing=:hntMaxColumnCpacing," +
                "hnt_maximum_floor_height=:hntMaximumFloorHeight,hnt_max_span_seam=:hntMaxSpanSeam,zh_floor_type=:zhFloorType,zh_roof_type=:zhRoofType,zh_wall_materials=:zhWallMaterials," +
                "zh_wall_thickness_interior=:zhWallThicknessInterior,zh_wall_thickness_exterior=:zhWallThicknessExterior,zh_mortar_type=:zhMortarType,zh_mortar_strength=:zhMortarStrength," +
                "zh_bearing_system=:zhBearingSystem,zh_bearing_mode=:zhBearingMode,zh_staircase_location=:zhStaircaseLocation,zh_hole_crner=:zhHoleCrner,zh_structural_column=:zhStructuralColumn," +
                "zh_ring_beam=:zhRingBeam,zm_roof_type=:zmRoofType,zm_wall_materials=:zmWallMaterials,zm_mortar=:zmMortar,zm_bearing_system=:zmBearingSystem,zm_wall_opening_area=:zmWallOpeningArea," +
                "zm_structural_column=:zmStructuralColumn,zm_ring_beam=:zmRingBeam,soil_hard_mountain=:soilHardMountain,soil_wall=:soilWall,soil_mode_type=:soilModeType,soil_wall_ring_beam=:soilWallRingBeam," +
                "soil_wall_ductile_material=:soilWallDuctileMaterial,soil_door_window_width=:soilDoorWindowWidth,soil_max_spacing=:soilMaxSpacing,soil_roof_form=:soilRoofForm,soil_roofing_materials=:soilRoofingMaterials," +
                "soil_roof_tying_measures=:soilRoofTyingMeasures,wood_structure_type=:woodStructureType,wood_columns_bracing=:woodColumnsBracing,wood_column_extension=:woodColumnExtension,wood_node=:woodNode," +
                "wood_tip_diameter=:woodTipDiameter,wood_foundation_anchoring=:woodFoundationAnchoring,wood_gable_wall=:woodGableWall,wood_enclosure_wall=:woodEnclosureWall,wood_wall_connection=:woodWallConnection," +
                "stone_wall_materials=:stoneWallMaterials,stone_load_mode=:stoneLoadMode,stone_floor_structure=:stoneFloorStructure,stone_roof_type=:stoneRoofType,stone_maximum_spacing=:stoneMaximumSpacing," +
                "stone_wall_opening_area=:stoneWallOpeningArea,stone_wall_connection=:stoneWallConnection,stone_ring_beam=:stoneRingBeam,stone_four_structural=:stoneFourStructural,is_submit=:isSubmit,submitter=:submitter,submitter_id=:submitterId,submit_time=:submitTime where id = '"+ buildingEntity.getId()+"\'");
        SqlParameterSource source = new BeanPropertySqlParameterSource(buildingEntity);
        return namedParameterJdbcTemplate.update(sql.toString(), source) > 0;
    }

    @Override
    public Map<String, Object> queryBuildingData(HttpServletRequest request, BuildingParams queryParams, int curPage, int pageSize) {
        StringBuilder sql = new StringBuilder("SELECT t1.id,t1.task_code,t1.building_structure,t1.province,t1.city,t1.county,t1.country,t1.build_code,t1.is_submit,t1.submitter,t1.submit_time,t2.task_name,t2.task_status from ypg_building_single t1 LEFT JOIN ypg_survey_task t2 on t1.task_code=t2.task_num ");
        StringBuilder whereSql = new StringBuilder("where t1.del_flag = '0' and t1.is_submit = '1' ");
        if(!PlatformObjectUtils.isEmpty(queryParams.getProvinceId())){
            whereSql.append(" and t1.province_id = '"+ queryParams.getProvinceId()+"\'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getCityId())){
            whereSql.append(" and t1.city_id = '"+ queryParams.getCityId()+"\'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getCountyId())){
            whereSql.append(" and t1.county_id = '"+ queryParams.getCountyId()+"\'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getBuildingStructure())){
            whereSql.append(" and t1.building_structure = '"+ queryParams.getBuildingStructure()+"\'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getBuildCode())){
            whereSql.append(" and t1.build_code like '%"+ queryParams.getBuildCode()+"%'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getTaskName())){
            whereSql.append(" and t2.task_name like '%"+ queryParams.getTaskName() +"%'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getStartTime()) && !PlatformObjectUtils.isEmpty(queryParams.getEndTime())){
            whereSql.append(" and t1.submit_time >= '"+ queryParams.getStartTime() + "' and t1.submit_time <= '"+ queryParams.getEndTime()+"\'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getSubmitter())){
            whereSql.append(" and t1.submitter like '%"+ queryParams.getSubmitter() +"%'");
        }
        StringBuilder orderSql = new StringBuilder(" order by t1.submit_time desc");

        int firstIndex=(curPage-1) * pageSize;
        int lastIndex=curPage * pageSize;
        StringBuilder limitSql = new StringBuilder(" limit "+lastIndex+" offset "+ firstIndex);
        sql.append(whereSql).append(orderSql);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(BuildingParams.class)).size();
        List<BuildingParams> buildingData = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(BuildingParams.class));
        Map<String,Object> map=new HashMap();
        map.put("total",total);
        map.put("rows",buildingData);
        return map;
    }

    @Override
    public boolean deleteBuilding(String id) {
        String sql = "update ypg_building_single set del_flag = 1  where id = ?";
       return jdbcTemplate.update(sql,id) > 0;
    }

}
